Release 10.1A: OpenEdge Data Management:
Database Administration

Saving disk space by deactivating indexes

You can free up disk space for an application by deactivating its indexes. Also, if you are dumping and loading a large database, you can speed up the operation by deactivating the indexes in one or more database tables. This technique should be used only in situations where indexes are used infrequently, such as in an application that generates reports only once or twice a year. There are two ways to deactivate indexes:

Deactivate individual indexes or all the indexes with the Data Administration tool if you are using a graphical interface, or the Data Dictionary if you are using a character interface.
Deactivate indexes from a Progress 4GL procedure.

You can deactivate (but not reactivate) a single index from either Data tool. If you create a new unique index on an existing file, consider deactivating the index. If existing table data yields duplicate keys, all changes made during the current session are backed out when you try to save them. Create or reactivate a unique index after you have ensured that all key values are unique.

To activate an index, use PROUTIL IDXBUILD. See Chapter 20, "PROUTIL Utility," for more information about activating indexes with PROUTIL IDXBUILD.

Once an index is deactivated, you cannot use it to retrieve information from the records it normally points to. If you attempt to use a FOR, FOR EACH, or CAN-FIND statement in connection with a deactivated index, the database engine terminates the currently executing program. However, you can create, delete, or update a record that a deactivated index points to.

The database engine does not examine the active status of an index when it makes a search. Nor does the active status of an index affect the time stamp on the _Index file. As a result, precompiled programs do not require compilation if the only change to the schema is index activation or deactivation.

To deactivate an individual index or all of the indexes using a Data Tool:

Access the Data tool.
Choose Utilities Deactivate Indexes. The Index Deactivation dialog box appears.
Choose OK. The Data tool lists the tables in the database.
Type all to deactivate all indexes, or select the indexes you want to deactivate. The Data tool prompts you to verify that you want to deactivate all the indexes.
Verify that you want to deactivate the specified indexes.

To deactivate an individual index from a graphical user interface:

Access the Data Dictionary tool.
Click the Index icon.
Click the index you want to deactivate.
Click the Index Properties button. The Index Properties dialog box appears.
Select the table that contains the index you want to deactivate. The Data Dictionary lists the indexes defined for the selected database.
Click the Active toggle box.
Click the Save button. The Data Dictionary prompts you to verify that you want to deactivate the index.
Verify that you want to deactivate the index. The Data Dictionary deactivates the index.

You can also deactivate an index from a Progress 4GL procedure. Search through the _Index file to find the index you want to deactivate, then set the _Active field equal to NO. The following example uses this technique:

FIND _Index WHERE _Index-Name = "cust-num".   IF (_Index._Active)   THEN _Index._Active = NO.   ELSE IF NOT(_Index._Active) THEN   MESSAGE "The specified index is Deactivated.".